﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Xml;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QuanLyBanHang.DataConnection
{
    class Connect
    {
        SqlConnection conn;
        public String conString;
        public static String sv;
        public static String CSDL;
        public static String user;
        public static String pass;
        // Phương thức kết nối sql
        public SqlConnection getConnect()
        {
            XmlDocument xmlread = new XmlDocument();
            xmlread.Load("Connection.xml");
            XmlElement xmlelement1 = xmlread.DocumentElement;
            sv = xmlelement1.SelectSingleNode("sv").InnerText;
            CSDL = xmlelement1.SelectSingleNode("database").InnerText;
            user = xmlelement1.SelectSingleNode("username").InnerText;
            pass = xmlelement1.SelectSingleNode("password").InnerText;
            if (user == "") // ko dung user - pass
                conString = @"Data Source = " + sv + ";Initial Catalog = " + CSDL + ";Integrated Security=SSPI;";
                //conString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\quanlybanhang.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            // return new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\QLKhachSan.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
            else
                conString = @"Data Source=" + sv + ";Initial Catalog=" + CSDL + ";User Id=" + user + ";Password=" + pass + ";";
                return new SqlConnection(conString);
            //return new SqlConnection(@"Data Source=PC\SQLEXPRESS;Initial Catalog=quanlybanhang;Integrated Security=True");
        }
        public bool checkConn()
        {
            conn = getConnect();
            try
            {
                conn.Open();
                if (conn.State != ConnectionState.Open)
                {
                    Application.Exit();
                    return false;
                }
                else
                    return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        // Phương thức trả về bảng select
        public DataTable getTable(String sql)
        {
            DataTable dt = new DataTable();
            try
            {
                
            SqlConnection conn = getConnect();
            SqlDataAdapter ad = new SqlDataAdapter(sql, conn);            
            ad.Fill(dt);
            
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return dt;
        }

        // Phương thức thực hiện them sua ...
        public void ExcuteQuerySql(string sql)
        {
            try
            {
                SqlConnection conn = getConnect();
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);                
            }
           
        }
    }
}
